using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using LeisureCenter.Api;
using LeisureCenter.Tools;

namespace LeisureCenter.BrowserItems
{
    public class LibraryAudioBrowserObjectItem : LibraryBrowserObjectItem
    {
        public LibraryAudioBrowserObjectItem(IBrowserFolderItem folder, string fileName, string text)
            : base(folder, fileName, text)
        {
        }

        public override string IconName
        {
            get { return @"music"; }
        }
    }

    public abstract class BaseLibraryAudioBrowserFolderItem : BaseLibraryBrowserFolderItem
    {
        protected BaseLibraryAudioBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.tables.Add("AudioFilesMetaData");
            this.wheres.Add(@"AudioFilesMetaData.Id = Files.Id");
            this.wheres.Add(@"(Files.Deleted ISNULL OR Files.Deleted = 0)");
        }

        protected override void  CreateGeneralItems()
        {
            this.items.Add(new LibraryAudioAllDynamicPlaylistFolderItem(this, @"All songs", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioTop100DynamicPlaylistFolderItem(this, @"Most listened songs top 100", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioPreferedDynamicPlaylistFolderItem(this, @"100 Preferred songs", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioPreferedAlbumsDynamicPlaylistFolderItem(this, @"100 Preferred albums", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioNew100DynamicPlaylistFolderItem(this, @"100 Newest songs", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioForgottenAlbumsDynamicPlaylistFolderItem(this, @"Forgotten albums", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
            this.items.Add(new LibraryAudioNewAlbumsDynamicPlaylistFolderItem(this, @"New albums", new QueryItems(this.tables, this.wheres, null, this.titleParts)));
        }
    }

    public class LibraryAudioAlbumBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioAlbumBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems, string album)
            : base(folder, text, queryItems)
        {
            this.wheres.Add(String.Format(@"AudioFilesMetaData.Album = '{0}'", SQLiteTools.EscapeSqlString(album)));
            this.titleParts.Add(album);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT DISTINCT filename, track, title FROM {0} {1} ORDER BY track COLLATE NOCASE", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string trackStr = "";
            object track = reader.GetValue(1);
            if (track != null && track is long && (long)track > 0)
                trackStr = ((long)track).ToString("00") + " - ";
            this.items.Add(new LibraryAudioBrowserObjectItem(this, reader.GetValue(0).ToString(), trackStr + reader.GetValue(2).ToString()));
        }
    }

    public class LibraryAudioArtistBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioArtistBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems, string artist)
            : base(folder, text, queryItems)
        {
            this.wheres.Add(String.Format(@"AudioFilesMetaData.Artist = '{0}'", SQLiteTools.EscapeSqlString(artist)));
            this.titleParts.Add(artist);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select distinct AudioFilesMetaData.Album from {0} {1} order by AudioFilesMetaData.Album collate NOCASE", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string album = reader.GetValue(0).ToString();
            this.items.Add(new LibraryAudioAlbumBrowserFolderItem(this, album, new QueryItems(this.tables, this.wheres, null, this.titleParts), album));
        }
    }

    public class LibraryAudioArtistsBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioArtistsBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select distinct AudioFilesMetaData.Artist from {0} {1} order by AudioFilesMetaData.Artist collate NOCASE", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string artist = reader.GetValue(0).ToString();
            this.items.Add(new LibraryAudioArtistBrowserFolderItem(this, artist, new QueryItems(this.tables, this.wheres, null, this.titleParts), artist));
        }
    }

    public class LibraryAudioAlbumsBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        private string having = "";

        public LibraryAudioAlbumsBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems, string having)
            : base(folder, text, queryItems)
        {
            this.having = having;
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT DISTINCT AudioFilesMetaData.Artist, AudioFilesMetaData.Album FROM {0} {1} GROUP BY artist, album HAVING {2} ORDER BY AudioFilesMetaData.Artist COLLATE NOCASE, AudioFilesMetaData.Album COLLATE NOCASE", GetTables(), GetWhereCondition(), this.having));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string artist = reader.GetValue(0).ToString();
            string album = reader.GetValue(1).ToString();
            List<string> wheres = new List<string>(this.wheres);
            wheres.Add(string.Format(@"artist = '{0}'", SQLiteTools.EscapeSqlString(artist)));
            this.items.Add(new LibraryAudioAlbumBrowserFolderItem(this, artist + " - " + album, new QueryItems(this.tables, wheres, null, this.titleParts), album));
        }
    }

    public class LibraryAudioGenresBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioGenresBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select distinct AudioFilesMetaData.Genre from {0} {1} order by AudioFilesMetaData.Genre collate NOCASE", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            List<string> wheres = new List<string>(this.wheres);
            string genre = reader.GetValue(0).ToString();
            wheres.Add(String.Format(@"genre = '{0}'", SQLiteTools.EscapeSqlString(genre)));
            List<string> titleParts = new List<string>(this.titleParts);
            titleParts.Add(genre);
            this.items.Add(new LibraryAudioArtistsBrowserFolderItem(this, genre, new QueryItems(this.tables, wheres, null, titleParts)));
        }
    }

    public class LibraryAudioPlaylistBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioPlaylistBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.tables.Add("PlaylistsItems");
            this.wheres.Add(@"PlaylistsItems.FileId = Files.id");
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT filename, artist, title FROM {0} {1} ORDER BY Position", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            this.items.Add(new LibraryAudioBrowserObjectItem(this, reader.GetValue(0).ToString(), reader.GetValue(1).ToString() + " - " + reader.GetValue(2).ToString()));
        }
    }

    public class LibraryAudioPlaylistsBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioPlaylistsBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.tables.Add("Playlists");
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT Name, Id FROM Playlists {0} ORDER BY Name COLLATE NOCASE", GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string name = reader.GetValue(0).ToString();
            List<string> wheres = new List<string>(this.wheres);
            string id = reader.GetValue(1).ToString();
            wheres.Add(string.Format(@"playlistId = {0}", id));
            List<string> titleParts = new List<string>(this.titleParts);
            titleParts.Add(name);
            this.items.Add(new LibraryAudioPlaylistBrowserFolderItem(this, name, new QueryItems(this.tables, wheres, null, titleParts)));
        }
    }

    public class LibraryAudioRatingsBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioRatingsBrowserFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
        }

        private void AddItem(string where, string title, bool artists)
        {
            List<string> wheres = new List<string>(this.wheres);
            List<string> titleParts = new List<string>(this.titleParts);
            titleParts.Add(title);
            if (artists)
            {
                wheres.Add(where);
                this.items.Add(new LibraryAudioArtistsBrowserFolderItem(this, title, new QueryItems(this.tables, wheres, null, titleParts)));
            }
            else
                this.items.Add(new LibraryAudioAlbumsBrowserFolderItem(this, title, new QueryItems(this.tables, wheres, null, titleParts), where));
        }

        protected override void CreateItems()
        {
            this.items = new List<IBrowserItem>();
            AddItem(@"rating = 5", @"*****", true);
            AddItem(@"rating >= 4", @"****+", true);
            AddItem(@"rating >= 3", @"***+", true);
            AddItem(@"rating >= 2", @"**+", true);
            AddItem(@"rating >= 1", @"*+", true);
            AddItem(@"rating ISNULL", @"Unrated", true);
            AddItem(@"avg(rating) >= 4", @"Albums ****+", false);
            AddItem(@"avg(rating) >= 3", @"Albums ***+", false);
            AddItem(@"avg(rating) >= 2", @"Albums **+", false);
            AddItem(@"avg(rating) >= 1", @"Albums *+", false);
            AddItem(@"rating = 4", @"****", true);
            AddItem(@"rating = 3", @"***", true);
            AddItem(@"rating = 2", @"**", true);
            AddItem(@"rating = 1", @"*", true);
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
        }
    }

    public abstract class BaseLibraryAudioDynamicPlaylistFolderItem : BaseLibraryBrowserFolderItem
    {
        protected BaseLibraryAudioDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.tables.Add("AudioFilesMetaData");
            this.wheres.Add(@"AudioFilesMetaData.Id = Files.Id");
            this.wheres.Add(@"(Files.Deleted ISNULL OR Files.Deleted = 0)");
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            this.items.Add(new LibraryAudioBrowserObjectItem(this, reader.GetValue(0).ToString(), reader.GetValue(1).ToString()));
        }

        public override string IconName { get { return @"library"; } }
    }

    public class LibraryAudioTop100DynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioTop100DynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.tables.Add("Playings");
            this.wheres.Add(@"Files.Id = Playings.Id");
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select filename, title from {0} {1} group by Playings.id order by count(Playings.id) collate NOCASE desc limit 100", GetTables(), GetWhereCondition()));
        }
    }

    public class LibraryAudioNew100DynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioNew100DynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select filename, title from {0} {1} order by addedDatetime collate NOCASE desc limit 100", GetTables(), GetWhereCondition()));
        }
    }

    public class LibraryAudioAllDynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioAllDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select filename, title from {0} {1}", GetTables(), GetWhereCondition()));
        }
    }

    public class LibraryAudioPreferedDynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioPreferedDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.wheres.Add(@"Files.Rating NOTNULL");
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"select filename, title from {0} {1} order by rating desc limit 100", GetTables(), GetWhereCondition()));
        }
    }

    public class LibraryAudioPreferedAlbumsDynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioPreferedAlbumsDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT artist, album FROM {0} {1} GROUP BY artist, album ORDER BY AVG(Files.rating) DESC limit 1000", GetTables(), GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string artist = reader.GetValue(0).ToString();
            string album = reader.GetValue(1).ToString();
            List<string> wheres = new List<string>(this.wheres);
            wheres.Add(string.Format(@"artist = '{0}'", SQLiteTools.EscapeSqlString(artist)));
            this.items.Add(new LibraryAudioAlbumBrowserFolderItem(this, artist + " - " + album, new QueryItems(this.tables, wheres, null, this.titleParts), album));
        }
    }

    public class LibraryAudioForgottenAlbumsDynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioForgottenAlbumsDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT artist, album from AudioFilesMetaData, Files LEFT OUTER JOIN Playings ON Playings.id = Files.id {0} GROUP BY artist, album ORDER BY MAX(Playings.datetime) limit 1000", GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string artist = reader.GetValue(0).ToString();
            string album = reader.GetValue(1).ToString();
            List<string> wheres = new List<string>(this.wheres);
            wheres.Add(string.Format(@"artist = '{0}'", SQLiteTools.EscapeSqlString(artist)));
            this.items.Add(new LibraryAudioAlbumBrowserFolderItem(this, artist + " - " + album, new QueryItems(this.tables, wheres, null, this.titleParts), album));
        }
    }

    public class LibraryAudioNewAlbumsDynamicPlaylistFolderItem : BaseLibraryAudioDynamicPlaylistFolderItem
    {
        public LibraryAudioNewAlbumsDynamicPlaylistFolderItem(IBrowserFolderItem folder, string text, QueryItems queryItems)
            : base(folder, text, queryItems)
        {
            this.titleParts.Add(text);
        }

        protected override void CreateItems()
        {
            ExecuteQuery(String.Format(@"SELECT artist, album from AudioFilesMetaData, Files {0} GROUP BY artist, album ORDER BY MAX(Files.AddedDatetime) DESC limit 100", GetWhereCondition()));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
            string artist = reader.GetValue(0).ToString();
            string album = reader.GetValue(1).ToString();
            List<string> wheres = new List<string>(this.wheres);
            wheres.Add(string.Format(@"artist = '{0}'", SQLiteTools.EscapeSqlString(artist)));
            this.items.Add(new LibraryAudioAlbumBrowserFolderItem(this, artist + " - " + album, new QueryItems(this.tables, wheres, null, this.titleParts), album));
        }
    }

    public class LibraryAudioBrowserFolderItem : BaseLibraryAudioBrowserFolderItem
    {
        public LibraryAudioBrowserFolderItem(long libraryId)
            : base(null, "", new QueryItems(null, null,  null, null))
        {
            if (libraryId != 0)
                this.wheres.Add("Files.LibraryId = " + libraryId);
        }

        protected override void CreateItems()
        {
            this.items = new List<IBrowserItem>();
            this.items.Add(new LibraryAudioGenresBrowserFolderItem(this, @"Genres", new QueryItems(this.tables, this.wheres, null, null)));
            this.items.Add(new LibraryAudioArtistsBrowserFolderItem(this, @"Artists", new QueryItems(this.tables, this.wheres, null, null)));
            this.items.Add(new LibraryAudioRatingsBrowserFolderItem(this, @"Ratings", new QueryItems(this.tables, this.wheres, null, new string[] { @"Ratings" })));
            this.items.Add(new LibraryAudioPlaylistsBrowserFolderItem(this, @"Playlists", new QueryItems(this.tables, this.wheres, null, null)));
        }

        protected override void ReadQueryItem(SQLiteDataReader reader)
        {
        }
    }
}